package com.backend_pay.dao;

import com.backend_pay.entity.IncomeDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.List;

@Repository //存储层bean，数据流层DAO
public interface IncomeDetailDao extends JpaRepository<IncomeDetail,Integer> {
    /*
    * @Query(value="select * from user where id=?",nativequery=true)
    * public void findById(int id);*/

    //查询所有收入 findAllById
    @Query(value="select * from income_detail where userid=?",nativeQuery=true)
    public List<IncomeDetail> findAllById(int userId);

    //添加一条incomeDetail
    @Modifying
    @Transactional
    @Query(value = "insert into income_detail(userid, type_income, money, comment,createtime) values (?1,?2,?3,?4,?5);",nativeQuery = true)
    public void addIncomeDetail(Integer userId, String typeIncome, Double money, String comment, Timestamp createtime);

    //查询所有收入类型
    @Query(value="select distinct type_income from income_detail ",nativeQuery=true)
    public String[] findAllKindOfIncome();
    //按条件查询收入记录
    @Query(value = "select * from income_detail where userid=?1 and type_income=?2  and createtime>?3 and createtime<?4",nativeQuery=true)
    public List<IncomeDetail> queryIncomeByCondition(Integer userid,String typeIncome,Timestamp start,Timestamp end);
    @Query(value = "select * from income_detail where userid=?1 and createtime>?2 and createtime<?3",nativeQuery=true)
    public List<IncomeDetail> queryIncomeByConditionNoType(Integer userid,Timestamp start,Timestamp end);

    //按条件查询收入的金额、收入类型
    @Query(value="select sum(cast(money as decimal(12,2))),type_income from income_detail where userid=?1 and createtime>?2 and createtime<?3 group by type_income",nativeQuery=true)
    public List<Object> queryIncomeByDate(Integer userid, Timestamp start, Timestamp end);

    //按条件查询收入的金额、时间
    @Query(value="select sum(cast(money as decimal(12,2))) as money,date_format(createtime,'%Y-%m-%d') as createtime from income_detail where userid=?1 and createtime>?2 and createtime<?3 group by date_format(createtime,'%Y-%m-%d') order by date_format(createtime,'%Y-%m-%d') desc",nativeQuery=true)
    public List<Object> queryIncomeGroupByDate(Integer userid, Timestamp start, Timestamp end);

    //删除某一用户的所有支出信息
    @Modifying
    @Transactional
    @Query(value="delete from income_detail where userid=?1",nativeQuery=true)
    public void deleteIncomeDetailByUserId(Integer userid);
}
